# coding:utf-8
import os

import jsonpath
import pymysql
import pymssql
from pymssql import OperationalError
from api.client import Read_Environment_info
from common.logger import Logger

# 读取环境变量数据库信息
def _db_info():
    try:
        return Read_Environment_info().environment(os.environ['api_test_env'])['db']
    except:
        return  Read_Environment_info().environment('single')['db']

# 获取连接方法
def mysql_conn():
    db = _db_info()
    conn = pymysql.connect(host=db['host'],  # 数据库地址
                           port=3306,  # 端口（配置文件传入的是字符串格式，所以这里取值的时候，用getint的方法 ）
                           user=db['user_name'],  # 账号
                           passwd=db['password'],  # 密码
                           database='ai_classroom',  # 要操作的数据库名
                           charset='utf8'
                           )  # 指定编码格式
    return conn

def mssql_conn():
    db = _db_info()
    try:
        connect = pymssql.connect(
            db['host'], db['user_name'], db['password'], db['engine_db_name'])  # 服务器名,账户,密码,数据库名
            # '10.5.11.43\SQL2016', 'sa', '95938', 'test_dotnet_bpm_engine')  # 服务器名,账户,密码,数据库名
        # print("连接成功!")
    except OperationalError as e:
        print('连接失败!')
        raise
    return connect

#查询
def sqlserver_query(sql):
    conn = mssql_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    Logger.info(f'执行sql{"*"*20}:\n{sql}')
    cur.execute(sql)  # 执行sql
    # conn.commit()
    result = cur.fetchone()  # 获取所有查询结果
    rows = []
    while result:  # 循环读取所有结果
        row = []
        for index in range(len(result)) :
            row.append(str(result[index]))
        rows.append(row)
        result = cur.fetchone()
    print(rows)
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    return rows  # 返回结果

#查询包含列名
def sqlserver_query_return_contain_field(sql,alias=''):
    """
    查询包含列名,result[0]是列名，后面index=1，2,3...开始是值
    :param sql:
    :param alias: 查询主题，方便打印后查看
    :return:
    """
    conn = mssql_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    Logger.info(f'执行sql{"*"*20}:\n{sql}')
    cur.execute(sql)  # 执行sql
    col = cur.description   # 获取列的相关信息
    result = cur.fetchone()  # 获取所有查询结果
    print(result)
    field = [i[0] for i in col] # col[*][0]就是列名
    rows = [field]
    while result:  # 循环读取所有结果
        row = []
        for index in range(len(result)) :
            row.append(str(result[index]))
        rows.append(row)
        result = cur.fetchone()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    print(f'****【{alias}】列表查询校验****：\n{rows}')
    return rows  # 返回结果

# 更新sqlserver
def sqlserver_update(sql):
    conn = mssql_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    try:
        Logger.info(f'执行更新sql：{sql}')
        cur.execute(sql)  # 执行sql
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭连接0

#查询
def query_db(sql):
    conn = mysql_conn()  # 获取连接
    # cur = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 建立游标
    cur = conn.cursor()  # 建立游标
    cur.execute(sql)  # 执行sql
    conn.commit()
    result = cur.fetchone()  # 获取所有查询结果
    result = str(result).replace(",","")  # 去掉逗号
    result = str(result).replace("(","")  # 去掉逗号
    result = str(result).replace(")","")  # 去掉逗号
    print(result)
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    return result  # 返回结果

# 更改
def change_db(sql):
    conn = mysql_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    try:
        cur.execute(sql)  # 执行sql
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭连接0

# 删除
def delete_db(sql):
    conn = mysql_conn()  # 获取连接
    cur = conn.cursor()  # 建立游标
    try:
        cur.execute(sql)  # 执行sql
        conn.commit()  # 提交更改
    except Exception as e:
        conn.rollback()  # 回滚
    finally:
        cur.close()  # 关闭游标
        conn.close()  # 关闭连接0

# 封装常用数据库操作
class sql:
    # 用于发起后，直接到审批状态，仅查询在办步骤
    def create_query_sql_by_id(self, proc_id):
        """
        新建流程后，通过流程id，查询后续步骤及节点信息，用于数校验，由于GUID随机，不包含各类GUID校验。[['序号', '步骤名称', '节点序号', '责任人', '节点状态', '步骤状态', '流程状态', '责任人code']]
        :param proc_id: 流程id
        :return:
        """
        sql = f'''SELECT spe.ActivityOrder '序号', spe.ActivityName '步骤名称', ne.OrderCode '节点序号', ne.AssignedToUserName '责任人', ne.TaskState '节点状态', spe.ActivityState '步骤状态', pe.ProcessState '流程状态', u.code '用户code'   
                FROM dbo.WfProcessInstance pe With (NoLock) JOIN dbo.WfTasks ne With (NoLock) ON pe.Id = ne.ProcessInstanceID 
                LEFT JOIN dbo.p_user u With (NoLock) ON u.id =ne.AssignedToUserID 
                JOIN WfActivityInstance spe With (NoLock) ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityInstanceID=spe.Id
                WHERE pe.Id = '{proc_id}' AND spe.ActivityState='1' ORDER BY spe.ActivityOrder,ne.OrderCode;'''
        return sql
    # 用于发起后，正常到审批状态校验，查询所有节点
    def create_process_query_all_master_nodes(self, proc_id):
        """
        新建流程后，通过流程id，查询所有步骤及节点信息，用于数校验，由于GUID随机，不包含各类GUID校验。[['序号', '步骤名称', '节点序号', '责任人', '节点状态', '步骤状态', '流程状态', '责任人code']]
        :param proc_id: 流程id
        :return:
        """
        sql = f'''SELECT spe.ActivityOrder '序号', spe.ActivityName '步骤名称', ne.OrderCode '节点序号', ne.AssignedToUserName '责任人', ne.TaskState '节点状态', spe.ActivityState '步骤状态', pe.ProcessState '流程状态', u.code '用户code'   
                FROM dbo.WfProcessInstance pe With (NoLock) JOIN dbo.WfTasks ne With (NoLock) ON pe.Id = ne.ProcessInstanceID 
                LEFT JOIN dbo.p_user u With (NoLock) ON u.id =ne.AssignedToUserID 
                JOIN WfActivityInstance spe With (NoLock) ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityInstanceID=spe.Id
                WHERE pe.Id = '{proc_id}' ORDER BY spe.ActivityOrder,ne.OrderCode;'''
        return sql

    # 用于发起后直接到归档校验
    def create_query_sql_history_by_id(self, proc_id):
        """
        新建流程后，通过流程id，查询历史表数据，新建时不包含各种guid校验。[['序号', '步骤名称', '节点序号', '责任人', '节点状态', '步骤状态', '流程状态', '责任人code']]
        :param proc_id: 流程id
        :return:
        """
        sql = f'''SELECT spe.ActivityOrder '序号',spe.ActivityName '步骤名称',ne.OrderCode '节点序号', ne.AssignedToUserName '责任人' ,ne.TaskState '节点状态', spe.ActivityState '步骤状态',pe.ProcessState '流程状态',u.code '用户code'
                        FROM dbo.WfProcessInstanceHistory pe With (NoLock) JOIN dbo.WfTasksHistory ne With (NoLock) ON pe.Id = ne.ProcessInstanceID 
                        LEFT JOIN dbo.p_user u With (NoLock) ON u.id =ne.AssignedToUserID 
                        JOIN dbo.WfActivityInstanceHistory spe With (NoLock) ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityInstanceID=spe.Id
                        WHERE pe.id = '{proc_id}' ORDER BY spe.ActivityOrder,ne.OrderCode;'''
        return sql

    # 用于查询出节点GUID和用户code，后续步骤审批，仅冒烟使用
    def approve_query_sql_by_id(self,proc_id):
        """
        审批流程后，通过流程id，查询后续步骤及节点信息。['序号', '步骤名称', '节点序号', '流程GUID', '流程名称', '责任人', '节点状态', '步骤状态', '流程状态', '节点GUID', '用户code']
        :param proc_id:
        :return:
        """
        sql = f"""SELECT spe.ActivityOrder '序号',spe.ActivityName '步骤名称',ne.OrderCode '节点序号', pe.Id '流程GUID',pe.ProcessName '流程名称', ne.AssignedToUserName '责任人' ,ne.TaskState '节点状态', spe.ActivityState '步骤状态',pe.ProcessState '流程状态',ne.Id '节点GUID',u.code '用户code'
                        FROM dbo.WfProcessInstance pe With (NoLock) JOIN dbo.WfTasks ne With (NoLock) ON pe.Id = ne.ProcessInstanceID 
                        LEFT JOIN dbo.p_user u With (NoLock) ON u.id =ne.AssignedToUserID 
                        JOIN WfActivityInstance spe With (NoLock) ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityInstanceID=spe.Id
                        WHERE pe.Id = '{proc_id}' AND ne.TaskState IN ('1','2') ORDER BY spe.ActivityOrder,ne.OrderCode;"""
        return sql

    def query_node_sql_by(self, process_name='', proc_id='', all_node:bool=True, task_table:bool=True, history:bool=False, other_field = ''):
        """
        新建或审批后，通过流程id或者流程名称，查询在办节点、所有节点、扩展节点、历史扩展节点、历史主节点信息.
        查询信息：['序号', '步骤名称', '节点序号', '审批意见', '流程GUID', '流程名称', '责任人', '节点状态', '步骤状态', '流程状态', '用户code']
        :param process_name: 流程名称
        :param proc_id: 流程id
        :param all_node: 是否查询所有节点信息，也就是是否加ne.TaskState IN (1,2)的过滤。默认查询所有
        :param task_table: 是否查询wfTasks表，或者查询wfExtTasks表。默认查询WfTasks
        :param history: 是否查询历史表，也就是history表。默认不查询历史
        :param other_field: 查询其他字段，需要拼接字段sql，如：,ne.EndedByName '处理人',ne.StationName '审批组织'
        :return: 返回拼接sql
        """
        if proc_id != '':
            condition = f"WHERE pe.id = '{proc_id}'"
        else:
            condition = f"WHERE pe.ProcessName = '{process_name}'"
        if history == False:
            flag = ''
        else:
            flag = 'History'
        if task_table == True:
            table = f'dbo.WfTasks{flag}'
        else:
            table = f'dbo.WfExtTasks{flag}'
        if all_node == True:
            and_condition = ""
        else:
            and_condition = f"AND ne.TaskState IN (1,2) "

        sql = f"""SELECT spe.ActivityOrder '序号', spe.ActivityName '步骤名称', ne.OrderCode '节点序号',ne.Opinion '审批意见', pe.Id '流程GUID', pe.ProcessName '流程名称', ne.AssignedToUserName '责任人', ne.TaskState '节点状态', spe.ActivityState '步骤状态',pe.ProcessState '流程状态', u.code '用户code'{other_field}
                        FROM dbo.WfProcessInstance{flag} pe With (NoLock) JOIN {table} ne With (NoLock) ON pe.Id = ne.ProcessInstanceID 
                        LEFT JOIN dbo.p_user u With (NoLock) ON u.id =ne.AssignedToUserID 
                        JOIN dbo.WfActivityInstance{flag} spe With (NoLock) ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityInstanceID=spe.Id
                        {condition} {and_condition}ORDER BY spe.ActivityOrder,ne.OrderCode;"""
        return sql

    def query_Todo(self,process_name='',proc_id=''):
        if process_name != '':
            sql = f"""SELECT ProcessName '流程名称',CurActivityName '当前步骤',CurAuditorUserName '当前责任人',ProcessState '流程状态',IsReminder '是否催办',ProcessLevel '是否加急' FROM WfProcessInstanceQueryTodo WITH(NOLOCK)
                                    WHERE ProcessName='{process_name}'"""
        else:
            sql = f"""SELECT ProcessName '流程名称',CurActivityName '当前步骤',CurAuditorUserName '当前责任人',ProcessState '流程状态',IsReminder '是否催办',ProcessLevel '是否加急' FROM WfProcessInstanceQueryTodo WITH(NOLOCK)
                                    WHERE ProcessInstanceID='{proc_id}'"""
        return sql

    def WfMessageLog_query_sql(self, process_name='', proc_id=''):
        """
        通过流程名称，查询WfMessageLog里面的消息；查询信息：['发送人', '接收人', '状态', '标题', '消息模式']。通过流程id查询，返回['发送人', '接收人', '状态', '消息模式']
        :param process_name:
        :param proc_id:
        :return:
        """
        if process_name != '':
            sql = f"""select SenderName '发送人',ReceiverName '接收人',State '状态',JSON_VALUE(FormatedContent,'$.other.subject') '标题',WakeMode '消息模式'
                            FROM WfMessageLog with(nolock) 
                            WHERE FormatedContent LIKE '%{process_name}%' ORDER BY CreationTime DESC;"""
            return sql
        elif proc_id != '':
            sql = f"""select SenderName '发送人',ReceiverName '接收人',State '状态',WakeMode '消息模式'
                        FROM WfMessageLog with(nolock) 
                        WHERE ProcessInstanceID ='{proc_id}' ORDER BY CreationTime DESC;"""
            return sql

    def WfMessageLogHistory_query_sql(self,process_name='', proc_id=''):
        """
        通过流程名称，查询WfMessageLogHistory里面的消息；查询信息：['发送人', '接收人', '状态', '标题', '消息模式']
        :param process_name:
        :return:
        """
        if process_name != '':
            sql = f"""select SenderName '发送人',ReceiverName '接收人',State '状态',JSON_VALUE(FormatedContent,'$.other.subject') '标题',WakeMode '消息模式'
                            FROM WfMessageLogHistory with(nolock) 
                            WHERE FormatedContent LIKE '%{process_name}%' ORDER BY CreationTime DESC;"""
            return sql
        elif proc_id != '':
            sql = f"""select SenderName '发送人',ReceiverName '接收人',State '状态',WakeMode '消息模式'
                                    FROM WfMessageLogHistory with(nolock) 
                                    WHERE ProcessInstanceID ='{proc_id}' ORDER BY CreationTime DESC;"""
            return sql


    def WfMessageLogMobile_query_sql_by_name(self,process_name):
        """
        通过流程名称，查询WfMessageLogMobile里面的消息；查询信息：['接收人', '状态', '标题', '消息模式']
        :param process_name:
        :return:
        """
        sql = f"""SELECT (SELECT Name FROM dbo.p_user WHERE id=Receiver) '接收人',Status '状态',FormatedContent '标题',WakeMode '消息模式'
                    FROM WfMessageLogMobile with(nolock) 
                    WHERE FormatedContent LIKE '%{process_name}%' ORDER BY CreatedDateTime DESC;"""
        return sql

    def WfMessageLogMobileHistory_query_sql_by_name(self,process_name):
        """
        通过流程名称，查询WfMessageLogMobileHistory里面的消息；查询信息：['接收人', '状态', '标题', '消息模式']
        :param process_name:
        :return:
        """
        sql = f"""SELECT (SELECT Name FROM dbo.p_user WHERE id=Receiver) '接收人',Status '状态',FormatedContent '标题',WakeMode '消息模式'
                    FROM WfMessageLogMobileHistory with(nolock) 
                    WHERE FormatedContent LIKE '%{process_name}%' ORDER BY CreatedDateTime DESC;"""
        return sql

    def p_TaskWake_query_sql_by_name(self,process_name):
        db = _db_info()
        """
        通过流程名称，查询p_TaskWake里面的消息；查询信息：['发送人', '接收人', '标题', '消息模式']
        :param process_name:
        :return:
        """
        sql = f"""SELECT (SELECT UserName FROM [{db['erp_db_name']}].dbo.myUser WHERE UserGUID=p.SendUserGuid) '发送人',my.UserName '接收人',p.WakeSubject '标题',p.WakeMode '消息模式'
                    FROM [{db['erp_db_name']}].dbo.myUser my 
                    INNER JOIN [{db['erp_db_name']}].dbo.p_TaskWake p ON my.UserGUID=p.UserGuid 
                    WHERE p.WakeSubject LIKE '%{process_name}%' ORDER BY p.CreatedTime DESC;"""
        return sql

    def p_TaskWakeHistory_query_sql_by_name(self,process_name):
        db = _db_info()
        """
        通过流程名称，查询p_TaskWakeHistory里面的消息；查询信息：['发送人', '接收人', '标题', '消息模式']
        :param process_name:
        :return:
        """
        sql = f"""SELECT (SELECT UserName FROM [{db['erp_db_name']}].dbo.myUser WHERE UserGUID=p.SendUserGuid) '发送人',my.UserName '接收人',p.WakeSubject '标题',p.WakeMode '消息模式'
                    FROM [{db['erp_db_name']}].dbo.myUser my 
                    INNER JOIN [{db['erp_db_name']}].dbo.p_TaskWakeHistory p ON my.UserGUID=p.UserGuid 
                    WHERE p.WakeSubject LIKE '%{process_name}%' ORDER BY p.CreatedTime DESC;"""
        return sql


if __name__ == '__main__':
    pass
    # execute = sql()
    # print(execute.query_node_sql_by(process_name='02不明确流程，分支明确，必须处理，手动归档-0032', all_node=False, task_table=False, history=True))
    # sql = execute.query_Todo('01不明确_下一步唯一_手动归档_下一步骤没有匹配责任人_必须处理-0003')
    # # sql = execute.create_query_sql_by_id('eff8756d-e396-b337-94f2-39ff5ddcdaab')
    # # sql = execute.WfMessageLog_query_sql_by_name('01发起明确路径_自动归档匹配到责任人-集团-2021-09-19-0003')
    # # sql = f'''SELECT spe.ActivityOrder '序号', spe.ActivityName '步骤名称', ne.OrderCode '节点序号', pe.Id '流程GUID', pe.ProcessName '流程名称', ne.AssignedToUserName '责任人', ne.TaskState '节点状态', spe.ActivityState '步骤状态',pe.ProcessState '流程状态', u.code '用户code'
    # #         FROM dbo.WfProcessInstance pe   JOIN dbo.WfTasks ne     ON pe.Id = ne.ProcessInstanceID LEFT JOIN test_dotnet_bpm_app.dbo.p_user u ON u.name =ne.AssignedToUserName
    # #         JOIN WfActivityInstance spe ON spe.ProcessInstanceID = ne.ProcessInstanceID AND ne.ActivityGUID=spe.ActivityGUID
    # #         WHERE pe.ProcessName = '01发起明确路径_自动归档匹配到责任人-01发起明确路径_自动归档匹配到责任人-集团-2021-08-02-0001' AND spe.ActivityState='1' ORDER BY spe.ActivityOrder,ne.OrderCode;'''
    # result = sqlserver_query_return_contain_field(sql)
    # print(result)
    # print(jsonpath.jsonpath(result,'$[*][-1:]'))
